package com.unionx.unionxuf.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Repository;

import com.unionx.unionxuf.common.DynamicConnect;
import com.unionx.unionxuf.common.TransformerStr;
import com.unionx.unionxuf.entity.Code;
import com.unionx.unionxuf.entity.Role;

@Repository("CustomerDao")
public class CustomerDaoImpl implements CustomerDao {

	private Statement stmt = null;
	private Connection conn = null;
	private Map<String, Object> map = new HashMap<String, Object>();
	
	public Object getCustomer(HttpServletRequest request) {
		
		map.clear();
		try {
			conn = (Connection) DynamicConnect.getConnectionInfo(request);
			stmt = conn.createStatement();
			String sql = "select cCusCode,cCusAbbName from Customer";
			List<Role> list = new ArrayList<Role>();
			
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
			Role r = new Role();
			r.setUsercode(rs.getString(1));
			r.setUsername(rs.getString(2));
				list.add(r);
			}
			map.put("status", 0);
			map.put("message", "方法请求成功");
			map.put("data", list);
			return map;
		} catch (SQLException e) {

			map.put("status", 1);
			map.put("message", "方法请求失败");
			map.put("data", "500");
			return map;
	
			
		}
	}

	public Object getXiaofei(HttpServletRequest request) {
		// TODO Auto-generated method stub
		map.clear();
		List<Code> list = new ArrayList<Code>();
		try {
			conn = (Connection) DynamicConnect.getConnectionInfo(request);
			stmt = conn.createStatement();
			String sql = "select top 4 ccode ,ccode_name from code where ccode like '%60010%'";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Code c = new Code();
			c.setCode(rs.getString(1));
			c.setCodename(rs.getString(2));
				list.add(c);
			}
			map.put("status", 0);
			map.put("message", "方法请求成功");
			map.put("data", list);
			return map;
		} catch (SQLException e){
				e.printStackTrace();
		}
		return map;
	}

	public Object getZixiaofei(HttpServletRequest request,String iname) {
		// TODO Auto-generated method stub
		map.clear();
		Code  c = new Code();
		try {
			
			conn = (Connection) DynamicConnect.getConnectionInfo(request);
			stmt = conn.createStatement();
			String sql = "select ccode from code where ccode_name  = '"+iname+"'";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
			c.setCode(rs.getString(1));
			}
			List<Code> list = new ArrayList<Code>();		
			StringBuffer sb =  new StringBuffer();
			sb.append("select  ccode,ccode_name from code");
			sb.append(" where");
			sb.append(" ccode like '%"+c.getCode()+"%'");
			sb.append(" and ccode not in");
			sb.append(" (select  top 1 ccode from code where ccode like '"+c.getCode()+"' )");
//select ccode ,ccode_name from code where ccode like '%600101%' and ccode not in (select  top 1 ccode from code where ccode like '%600101%' );

			String sqls = sb.toString();
			ResultSet rss = stmt.executeQuery(sqls);
			while(rss.next()){
				Code cs = new Code();
				cs.setCode(rss.getString(1));
				cs.setCodename(rss.getString(2));
				list.add(cs);
			}
			map.put("status", 0);
			map.put("message", "方法请求成功");
			map.put("data", list);
			return map;
		} catch (SQLException e){
				e.printStackTrace();
		}
		return map;
	}


}
